package elicecn.stock;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class ConvertData extends AcquireStockPrice {
	public static Connection createOracleConnection()
			throws ClassNotFoundException, SQLException {
		String driverClass = "oracle.jdbc.driver.OracleDriver";
//		String url = "jdbc:oracle:thin:@10.239.137.103:1521:oraxgx2";
		String url = "jdbc:oracle:thin:@10.239.137.151:1521:oraxgx1";
		String user = "sap";
		String password = "oracle";
		return getConnection(driverClass, url, user, password);
	}

	// sql 查询
	// columns [0]主键 [1]汉字名称 [2]拼音名称 [3]汉字名称 [4]拼音名称 ...
	public static void convertToPinyin(String tableName, String... columns)
			throws ClassNotFoundException, SQLException {
		Connection cnt = createOracleConnection();
		Connection conn = createOracleConnection();

		Statement stt = cnt.createStatement();
		String countSql = "select count(1) from " + tableName;
		ResultSet rs = stt.executeQuery(countSql);
		int total = 0;
		if (rs.next()) {
			total = rs.getInt(1);
		}
		rs.close();
		stt.close();

		String sql = "select " + columns[0];
		if (columns[1].equals("COMP_NAME")) {// 一个例外 号库表
			sql += ",LOOK_KEY";
		}
		for (int i = 1; i < columns.length; i += 2) {
			sql += ", " + columns[i];
		}
		sql += " from " + tableName;
		// System.out.println(sql);
		System.out.println(sql);

		stt = cnt.createStatement();
		rs = stt.executeQuery(sql);
		int k = 1;
		String pk = null;
		while (rs.next()) {
			try {
				String[] pinyin = new String[columns.length / 2];
				pk = rs.getString(1);
				for (int i = 1, j = 0; i < columns.length; i += 2, j++) {
					String v = rs.getString(columns[i]);
					if (v != null && v.length() > 20) {
						v = v.substring(0, 20);
					}
					pinyin[j] = Pinyin.toPinyin(v);
					if (columns[i].equals("COMP_NAME")) {
						String lk = rs.getString("LOOK_KEY");
						pinyin[j] = Pinyin.toPinyin(lk) + pinyin[j];
					}
					if (pinyin[j].length() > 900) {
						pinyin[j] = pinyin[j].substring(0, 900);
					}
				}
				String update = "update " + tableName + " set ";
				for (int i = 1, j = 0; i < columns.length; i += 2, j++) {
					update += columns[i + 1] + "='"
							+ replaceAllQuotes(pinyin[j]) + "'";
					if (i + 2 < columns.length) {
						update += ",";
					}
				}
				update += " where " + columns[0] + "='" + pk + "'";
				// System.out.println(update);

				Statement sttm = conn.createStatement();
				sttm.executeUpdate(update);
				sttm.close();

				pinyin = null;
				pk = null;
				update = null;
				sttm = null;

				k++;// 计数
				if (k % 1000 == 0) {
					System.out.println(tableName + ": " + k + "/" + total);
				}
			} catch (SQLException e) {
				System.out.println(k + pk);
				e.printStackTrace();
			}
		}
		conn.close();
		cnt.close();
	}

	// sql 查询
	// columns [0]主键 [1]汉字名称 [2]拼音名称 [3]汉字名称 [4]拼音名称 ...
	public static void convertToPinyinHeaderAndFull(String tableName,
			String... columns) throws ClassNotFoundException, SQLException {
		Connection cnt = createOracleConnection();
		Connection conn = createOracleConnection();

		Statement stt = cnt.createStatement();
		String countSql = "select count(1) from " + tableName;
		ResultSet rs = stt.executeQuery(countSql);
		int total = 0;
		if (rs.next()) {
			total = rs.getInt(1);
		}
		rs.close();
		stt.close();

		String sql = "select " + columns[0];
		for (int i = 1; i < columns.length; i += 2) {
			sql += ", " + columns[i];
		}
		sql += " from " + tableName;
		// System.out.println(sql);

		stt = cnt.createStatement();
		rs = stt.executeQuery(sql);
		int k = 1;
		String pk = null;
		while (rs.next()) {
			try {
				String[] pinyin = new String[columns.length / 2];
				pk = rs.getString(1);
				for (int i = 1, j = 0; i < columns.length; i += 2, j++) {
					String v = rs.getString(columns[i]);
					if (v != null && v.length() > 20) {
						v = v.substring(0, 20);
					}
					pinyin[j] = Pinyin.toPinyinHeader(v);
					if (pinyin[j].length() > 900) {
						pinyin[j] = pinyin[j].substring(0, 900);
					}
				}
				String update = "update " + tableName + " set ";
				for (int i = 1, j = 0; i < columns.length; i += 2, j++) {
					update += columns[i + 1] + "='"
							+ replaceAllQuotes(pinyin[j]) + "'";
					if (i + 2 < columns.length) {
						update += ",";
					}
				}
				update += " where " + columns[0] + "='" + pk + "'";
				// System.out.println(update);

				Statement sttm = conn.createStatement();
				sttm.executeUpdate(update);
				sttm.close();

				pinyin = null;
				pk = null;
				update = null;
				sttm = null;

				k++;// 计数
				if (k % 1000 == 0) {
					System.out.println(tableName + ": " + k + "/" + total);
				}
			} catch (SQLException e) {
				System.out.println(k + pk);
				e.printStackTrace();
			}
		}
		conn.close();
		cnt.close();
	}

	public static String replaceAllQuotes(String str) {
		return str.replaceAll("'", "''");
	}

	public static void convert() throws ClassNotFoundException, SQLException {
		// 号库
		convertToPinyin("sap.NA_COMPANY_INFO_tmp", "COMP_CODE", "COMP_NAME",
				"COMP_PY");
//		// 企业
//		convertToPinyin("sap.NA_PHONECOMP", "COMP_CODE", "COMP_NAME", "COMP_PY");
//		// 关键字
//		convertToPinyinHeaderAndFull("sap.NA_CODE_KEY", "RECODE_ID", "KEYWORD", "KEYWORD_YP");
//		// 秘书
//		convertToPinyinHeaderAndFull("sap.NA_COMP_SEC_PHONE", "RECODE_ID", "PHONE_NAME",
//				"NAME_PY");
//		// 行业
//		convertToPinyinHeaderAndFull("sap.NA_CODE_BUSI", "INDUSTRY_ID", "INDUSTRY_NAME",
//				"INDUSTRY_PY");
	}

	public static void main(String[] args) throws ClassNotFoundException,
			SQLException {
		convert();
	}
}
